package tool;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.beanutils.BeanUtils;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;

/**
 * 自己使用反射封装的mysql数据库操作类
 * 实现了数据的增删改查
 *
 * @author 拾柒
 * @date 2025/3/18
 * Api :
 * findList(String sql, Class<T> c) return ArrayList<实体类>--->获取表中所有数据
 * queryBean(String sql,Class<T> cls,Object... parameters) return <T>泛型数据------>查询表中的一组数据
 * Bean(String sql,Class<T> cls,Object... parameters)  return arrayList----->查询表中的多组数据
 * update1(sql,参数) return Integer --> 实现更新，删除，插入
 * queryMap(String sql,Object...parameters) return stringObjectHashMap----->查询表中的一组数据以Hash Map形式输出
 * queryMapList(String sql, Object...parameters) return ArrayList<Map>------>把多个Map对象以集合形式输出
 * queryArray(String sql, Object...parameters)  return Object[]------>查询表中的一组数据以数组形式输出
 * queryArrays(String sql, Object...parameters) return  ArrayList<Object[]>------>把多个数组对象以集合形式输出
 */
public class MyhandleDbReflection {
    /**
     * 获取数据库链接
     *
     * @return
     * @throws Exception
     */
    public static Connection conn() {
        Connection conn = null;
        try {
            Properties properties = new Properties();
            conn = null;
            FileInputStream fileInputStream = new FileInputStream("src/main/resources/jdbc.properties");
            properties.load(fileInputStream);
            //使用德鲁伊数据库连接池技术
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            conn = dataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return conn;
    }

    /**
     * 预编译
     * 创建 PreparedStatement 对象并设置 SQL 语句中的参数
     *
     * @param connection 数据库连接对象
     * @param sql        SQL 语句
     * @param parameters SQL 语句中的参数
     * @return 配置好参数的 PreparedStatement 对象
     * @throws SQLException 如果在创建 PreparedStatement 或设置参数时发生 SQL 错误
     */
    private static PreparedStatement PreSta(Connection connection, String sql, Object... parameters) {
        PreparedStatement Statement = null;
        try {
            Statement = connection.prepareStatement(sql);
            //获取字符串中参数个数
            ParameterMetaData parameterMetaData = Statement.getParameterMetaData();
            int num = parameterMetaData.getParameterCount();
            for (int i = 0; i < num; i++) {
                Statement.setObject(i + 1, parameters[i]);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Statement;
    }

    /**
     * 查询一张表的所有数据
     *
     * @param <T> 泛型类型
     * @param sql SQL 查询语句
     * @param c   要封装的对象类型
     * @return 封装好的对象列表
     */
    public static <T> List<T> findList(String sql, Class<T> c) {
        // 创建集合，存储所有对象数据
        List<T> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //获取数据库链接
            conn = conn();
            //创建PreparedStatement 并设置参数
            preparedStatement = PreSta(conn, sql);
            //执行查询
            resultSet = preparedStatement.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //获取列数
            int count = metaData.getColumnCount();
            // 解析结果集合
            while (resultSet.next()) {
                // 动态获取一行数据之后，封装成实体对象
                T t = c.getDeclaredConstructor().newInstance();
                for (int i = 1; i <= count; i++) {
                    // 通过列的序号获取列值
                    Object value = resultSet.getObject(i);
                    if (value != null) {
                        // 通过列的序号获取列名
                        String columnName = metaData.getColumnName(i);
                        try {
                            Field declaredField = c.getDeclaredField(columnName);
                            declaredField.setAccessible(true); // 允许访问 javabean 私有属性
                            declaredField.set(t, value);
                        } catch (NoSuchFieldException e) {
                            // 忽略该字段，继续处理下一个字段
                            continue;
                        }
                    }
                }
                list.add(t); // 将封装好的对象添加到列表中
            }
        } catch (SQLException | InstantiationException | IllegalAccessException |
                InvocationTargetException | NoSuchMethodException e) {
            e.printStackTrace();
        } finally {
            close(conn, resultSet, preparedStatement);
        }
        return list;
    }

    /**
     * 查询单组数据
     *
     * @param sql        sql语句
     * @param cls        实体类
     * @param parameters sql语句中的参数
     * @param <T>        泛型
     * @return 泛型数据
     */
    public static <T> T queryBean(String sql, Class<T> cls, Object... parameters) {
        //1.定义需要的对象
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        T t = null;

        try {
            //2.链接数据库
            conn = MyhandleDbReflection.conn();
            statement = PreSta(conn, sql, parameters);

            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            while (resultSet.next()) {
                t = cls.getConstructor().newInstance();
                //获取这一行记录字段的个数
                int count = metaData.getColumnCount();
                for (int i = 1; i <= count; i++) {
                    //表中的记录映射到javabean实体类的每一个属性上
                    BeanUtils.setProperty(t,
                            metaData.getColumnName(i),
                            resultSet.getObject(i));
                }
            }
            close(conn, resultSet, statement);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return t;
    }

    /**
     * 查找多组数据
     *
     * @param sql        SQL 查询语句
     * @param cls        要封装的对象类型
     * @param parameters SQL 查询语句中的参数
     * @param <T>        泛型类型
     * @return 封装好的对象列表
     */
    public static <T> List<T> Bean(String sql, Class<T> cls, Object... parameters) {
        //创建集合，存储所有对象数据
        ArrayList<T> arrayList = new ArrayList<>();
        //1.定义需要的对象
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        T t = null;

        try {
            //2.链接数据库
            conn = MyhandleDbReflection.conn();
            //创建PreparedStatement 并设置参数
            statement = PreSta(conn, sql, parameters);
            //执行查询
            resultSet = statement.executeQuery();
            //获取结果集的原数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            while (resultSet.next()) {
                t = cls.getConstructor().newInstance();
                //获取这一行记录字段的个数
                int count = metaData.getColumnCount();
                for (int i = 1; i <= count; i++) {
                    //表中的记录映射到javabean实体类的每一个属性上
                    BeanUtils.setProperty(t,
                            metaData.getColumnName(i),
                            resultSet.getObject(i));
                }
                arrayList.add(t);
            }
            close(conn, resultSet, statement);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
        return arrayList;
    }

    /**
     * @param sql
     * @param parameters
     * @return
     */
    public static int update(String sql, Object... parameters) {
        int f = 0;
        try {
            //获取连接
            Connection connection = MyhandleDbReflection.conn();
            PreparedStatement statement = MyhandleDbReflection.PreSta(connection, sql, parameters);
            f = statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return f;
    }

    /**
     * 获取表中某一条记录，以hashMap形式(键值对)存储返回
     *
     * @param sql        SQL 查询语句
     * @param parameters SQL 查询语句中的参数
     * @return 封装好的对象列表
     */
    public static HashMap<String, Object> queryMap(String sql, Object... parameters) {
        HashMap<String, Object> stringObjectHashMap = null;
        try {
            Connection conn = MyhandleDbReflection.conn();
            PreparedStatement preparedStatement = PreSta(conn, sql, parameters);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();
            stringObjectHashMap = new HashMap<>();
            while (resultSet.next()) {
                for (int i = 0; i < count; i++) {
                    stringObjectHashMap.put(metaData.getColumnName(i + 1), resultSet.getObject(i + 1));
                }
            }
            close(conn, resultSet, preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stringObjectHashMap;
    }

    /**
     * 把多个hashmap对象封装到list集合中
     *
     * @param sql
     * @param parameters
     * @return
     */
    public static ArrayList<Map> queryMapList(String sql, Object... parameters) {
        ArrayList<Map> maps = null;
        try {
            Connection conn = MyhandleDbReflection.conn();
            PreparedStatement preparedStatement = PreSta(conn, sql, parameters);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();
            maps = new ArrayList<>();
            while (resultSet.next()) {
                HashMap<String, Object> stringObjectHashMap = new HashMap<>();
                for (int i = 0; i < count; i++) {
                    stringObjectHashMap.put(metaData.getColumnName(i + 1), resultSet.getObject(i + 1));
                }
                maps.add(stringObjectHashMap);
            }
            close(conn, resultSet, preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return maps;
    }

    /**
     * 把单个数组对象以集合的存储形式返回
     *
     * @param sql
     * @param parameters
     * @return
     */
    public static Object[] queryArray(String sql, Object... parameters) {
        Object[] objects = new Object[0];
        try {
            Connection conn = MyhandleDbReflection.conn();
            PreparedStatement preparedStatement = PreSta(conn, sql, parameters);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();
            objects = new Object[count];
            resultSet.next();
            for (int i = 0; i < count; i++) {
                objects[i] = resultSet.getObject(i + 1);
            }
            close(conn, resultSet, preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return objects;
    }

    /**
     * 把多个数组对象以集合的存储形式返回
     *
     * @param sql
     * @param parameters
     * @return
     */
    public static ArrayList<Object[]> queryArrays(String sql, Object... parameters) {
        ArrayList<Object[]> maps = null;
        try {
            Connection conn = MyhandleDbReflection.conn();
            PreparedStatement preparedStatement = PreSta(conn, sql, parameters);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();
            maps = new ArrayList<Object[]>();
            while (resultSet.next()) {
                Object[] objects = new Object[count];
                for (int i = 0; i < count; i++) {
                    objects[i] = resultSet.getObject(i + 1);
                }
                maps.add(objects);
            }
            close(conn, resultSet, preparedStatement);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return maps;
    }

    /**
     * 关闭资源
     *
     * @param conn
     * @param resultSet
     * @param preparedStatement
     */
    public static void close(Connection conn, ResultSet resultSet, PreparedStatement preparedStatement) {
        try {
            if (conn != null) {
                conn.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //用户登录判断
    public static boolean isAdmin() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, username, password);
        Connection connection1 = MyhandleDbReflection.conn();
        System.out.println("请输入账户名");
        String user = new Scanner(System.in).next();
        System.out.println("请输入密码");
        String pwd = new Scanner(System.in).next();
        String sql = "select * from manager where admin=? and password=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //设置？的值
        preparedStatement.setString(1, user);
        preparedStatement.setString(2, pwd);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            System.out.println("登陆成功");
            return true;
        } else {
            System.out.println("非法管理员");
            return false;
        }
    }
    public static String getCap() {
        StringBuilder code = new StringBuilder();
        Random random = new Random();
        for (int i = 0; i < 4; i++) {
            code.append(random.nextInt(10));
        }
        return code.toString();
    }

}
